%macro get_filing_status;

	
	proc printto log = "&logdir/get_filing_status.txt" new;
	run;
	
	
	proc sort data = rlib.soi out = all nodupkey;
		by prim_tin sec_tin;
	run;
	
		data _null_;
			set all;
			file "&tmpdir./tinlist" dlm=',';
			put prim_tin sec_tin mars; 
		run;



		
		/****************************************
		*										*
		*			Start PROC SQL				*
		*										*
		****************************************/
		proc sql noprint;
		[connection details redacted]
			create table prim_to_prim as
			select * from connection to iq 
			(
			/* 2019 primary is 2018 primary */
			select
				a.prim_tin,
				a.sec_tin,
				b.[redacted] as prim_tin_x,
				case when b.[redacted] = 3 then 0 else b.[redacted] end as sec_tin_x,
				b.[redacted] as filing_status
			from #tinlist as a 
				inner join [redacted] as b
					on(a.prim_tin = b.[redacted])
			where b.[redacted] = &base_yr-1
		);
		
			/* 2019 primary is 2018 secondary */
			create table prim_to_sec as 
			select * from connection to iq 
			(
			select 
				a.prim_tin,
				a.sec_tin,
				b.[redacted] as prim_tin_x,
				b.[redacted] as sec_tin_x,
				b.[redacted] as filing_status
			from #tinlist as a 
				inner join [redacted] as b 
					on(a.prim_tin = b.[redacted])
			where b.[redacted] = &base_yr-1
				and a.prim_tin > 0
				and b.[redacted] = 2
			);
	
			create table sec_to_prim as
			select * from connection to iq 
			(
			/* 2019 secondary is 2018 primary */
			select
				a.prim_tin,
				a.sec_tin,
				b.[redacted] as prim_tin_x,
				case when b.[redacted] = 3 then 0 else b.[redacted] end as sec_tin_x,
				b.[redacted] as filing_status
			from #tinlist as a 
				inner join [redacted] as b
					on(a.sec_tin = b.[redacted])
			where b.[redacted] = &base_yr-1
				and a.sec_tin > 0
		);
		
			/* 2019 secondary is 2018 secondary */
			create table sec_to_sec as 
			select * from connection to iq 
			(
			select 
				a.prim_tin,
				a.sec_tin,
				b.[redacted] as prim_tin_x,
				b.[redacted] as sec_tin_x,
				b.[redacted] as filing_status
			from #tinlist as a 
				inner join [redacted] as b 
					on(a.sec_tin = b.[redacted])
			where b.[redacted] = &base_yr-1
				and a.sec_tin > 0
				and b.[redacted] = 2
			);
	disconnect from iq;
	
	
	/********************************
	*		Append them all			*
	********************************/
	/* 
	if we have an observation with prim_tin = prim_tin_x and sec_tin = sec_tin_x, keep that one 
	*/
	data appended;
		set prim_to_prim prim_to_sec sec_to_prim sec_to_sec;
		same = prim_tin = prim_tin_x & sec_tin = sec_tin_x;
		tu_match = same | (prim_tin = sec_tin_x & sec_tin = prim_tin_x);
	run;
	
	proc sort data = appended;
		by prim_tin sec_tin prim_tin_x sec_tin_x;
	run;
	
	proc means data = appended nway noprint;
		class prim_tin sec_tin;
		output out = any_same(drop=_type_ _freq_)
			max(same tu_match) = any_same any_tu_match;
	run;
	
	data appended;
		merge appended any_same;
		by prim_tin sec_tin;
		if any_same = 1 & same = 0 then delete;
		if any_tu_match = 1 & tu_match = 0 then delete;
		drop any_same any_tu_match same;
	run;
	
	/* now, just drop dups */
	proc sort data = appended nodupkey
		out = fs;
		by prim_tin sec_tin prim_tin_x sec_tin_x;
	run;
	


	/********************************
	*		Merge onto rlib.soi		*
	********************************/

	
	proc sort data = rlib.soi nodupkey; /* there really should be no dups here... VERIFIED */
		by prim_tin sec_tin;
	run;

	/* note that we may have multiple observations for each prim_tin, sec_tin in fs!
	
		This occurs when this year's tax unit reflects several tax units from last year. (E.g.,
		marriage occurring in 2019)
	
	*/
	
	data fs;
		merge rlib.soi(in=a) fs(in=b);
		by prim_tin sec_tin;
		if a;
		
		nonfiler_x = 1 - b;
	run;
	
	/****************************
	*	Append the nonfilers	*
	****************************/
	
	
	data rlib.filing_status;
		set fs rlib.nonfilers(in=b);
		nonfiler = b;
	run;
	proc sort data = rlib.filing_status;
		by prim_tin sec_tin;
	run;
	
	
	
	
	
	
	
	

	
	proc printto;
	run;



%mend;